package com.wss.dao;

import com.wss.domain.Video;
import com.wss.domain.VideoType;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 视频dao接口
 */
@Repository
public interface VideoDao {

    /**
     * 查询出所有视频
     * @return
     */
    @Select("select video.*,videotype.vtypename from video inner join videotype on (video.vtype_id = videotype.id) order by video.id")
    List<Video> findVideoAll();

    /**
     * 查询出最新的8条视频
     * @return
     */
    @Select("select video.*,vtypename from video inner join videotype on (video.vtype_id = videotype.id) order by id desc limit 8")
    List<Video> findVideoTop8();

    /**
     * 根据id查询视频
     * @param id
     * @return
     */
    @Select("select * from video where id = #{id}")
    Video findVideoById(int id);

    /**
     * 根据类型id查询视频
     * @param vtype_id
     * @return
     */
    @Select("select * from video where video.vtype_id = #{vtype_id} order by video.id")
    List<Video> findVideoByTypeId(int vtype_id);

    /**
     * 根据用户id查询购买的课程
     * @param u_id
     * @return
     */
    @Select("select video.* from video inner join orders on (orders.u_id = #{u_id} and orders.v_id = video.id) order by video.id")
    List<Video> findVideoByUid(int u_id);

    /**
     * 模糊查询视频标题，视频分类，视频简介
     * @param input
     * @return
     */
    @Select("select video.* from video inner join videotype on (vtypename like concat('%',#{input},'%') or vtitle like concat('%',#{input},'%') or vdesc like concat('%',#{input},'%')) and (video.vtype_id = videotype.id) order by video.id")
    List<Video> findVideoBySearchInput(@Param("input") String input);

    /**
     * 添加视频
     * @param video
     */
    @Insert("insert into video (vtitle,vurl,vcoverurl,vdesc,vtime,vprice,vtype_id) values (#{vtitle},#{vurl},#{vcoverurl},#{vdesc},curdate(),#{vprice},#{vtype_id})")
    void addVideo(Video video);

    /**
     * 修改视频
     * @param video
     */
    @Update("update video set vtitle=#{vtitle},vurl=#{vurl},vcoverurl=#{vcoverurl},vdesc=#{vdesc},vtime=curdate(),vprice=#{vprice},vtype_id=#{vtype_id} where id=#{id}")
    void changeVideo(Video video);

    /**
     * 删除视频
     * 先删除视频表关联的评论表和订单表内容，最后再删除视频表数据
     * @param id
     * @return
     */
    @Delete("delete comments from video inner join comments on (video.id=comments.v_id) where video.id=#{id}")
    int deleteCommentsWithVid(int id);
    @Delete("delete orders from video inner join orders on (video.id=orders.v_id) where video.id=#{id}")
    int deleteOrdersWithVid(int id);
    @Delete("delete video from video where id=#{id}")
    int deleteVideo(int id);

}
